USE [SONALIKA_BGK_UPDATED]
GO

/****** Object:  StoredProcedure [dbo].[SpTractorPurchaseRpt]    Script Date: 05/29/2014 00:32:49 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SpTractorPurchaseRpt]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SpTractorPurchaseRpt]
GO

USE [SONALIKA_BGK_UPDATED]
GO

/****** Object:  StoredProcedure [dbo].[SpTractorPurchaseRpt]    Script Date: 05/29/2014 00:32:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE PROC [dbo].[SpTractorPurchaseRpt]-- '1-1-2013','1-1-2015',2
(@FROM_DATE DATETIME,
@TO_DATE DATETIME,
@TRACTOR_MODEL_ID INT)
AS
BEGIN
IF (@TRACTOR_MODEL_ID <>0)
BEGIN
SELECT DISTINCT INVOICE_NO
	    ,replace(convert(NVARCHAR, INVOICE_DATE, 106), ' ', '-')AS INVOICE_DATE  
       ,INVOICE_GRANDTOTAL
       ,SUPPLIER_NAME,
       TP.INVOICE_ID AS INVOICE_ID
    
       
 FROM TRACTOR_PURCHASES TP
  LEFT OUTER JOIN INVOICE I ON I.INVOICE_ID=TP.INVOICE_ID
  LEFT OUTER JOIN SUPPLIER S ON S.SUPPLIER_ID=TP.SUPPLIER_ID
  LEFT OUTER JOIN TRACTOR_MODELS TM ON TM.TRACTOR_MODEL_ID=TP.TRACTOR_MODEL_ID
   
            
 WHERE PURCHASE_TYPE=(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='TRACTOR')
      AND(INVOICE_DATE BETWEEN @FROM_DATE AND @TO_DATE)
      AND TP.TRACTOR_MODEL_ID=@TRACTOR_MODEL_ID
     
END
ELSE
BEGIN
SELECT DISTINCT INVOICE_NO
	    ,replace(convert(NVARCHAR, INVOICE_DATE, 106), ' ', '-')AS INVOICE_DATE  
       ,INVOICE_GRANDTOTAL
       ,SUPPLIER_NAME,
       TP.INVOICE_ID AS INVOICE_ID
    
       
 FROM TRACTOR_PURCHASES TP
  LEFT OUTER JOIN INVOICE I ON I.INVOICE_ID=TP.INVOICE_ID
  LEFT OUTER JOIN SUPPLIER S ON S.SUPPLIER_ID=TP.SUPPLIER_ID
            
 WHERE PURCHASE_TYPE=(SELECT MASTER_ID FROM MASTER WHERE MASTER_VALUE='TRACTOR')AND(INVOICE_DATE BETWEEN @FROM_DATE AND @TO_DATE)
     
END
END


GO


